﻿/*
Navicat SQL Server Data Transfer

Source Server         : LOCALHOST
Source Server Version : 110000
Source Host           : .:1433
Source Database       : AAACHAM2
Source Schema         : dbo

Target Server Type    : SQL Server
Target Server Version : 110000
File Encoding         : 65001

Date: 2014-04-22 23:26:17
*/


-- ----------------------------
-- Table structure for tb0038
-- ----------------------------
DROP TABLE [dbo].[tb0038]
GO
CREATE TABLE [dbo].[tb0038] (
[ID] int IDENTITY(1,1) NOT NULL,
[DivisionID] varchar(20) NOT NULL ,
[DivisionName] nvarchar(250) NULL ,
[Disabled] TINYINT NOT NULL DEFAULT ((0)) ,
[Tel] varchar(50) NULL ,
[Fax] varchar(50) NULL ,
[Email] varchar(100) NULL ,
[Address] nvarchar(250) NULL ,
[ContactPerson] nvarchar(50) NULL ,
[VATNO] varchar(50) NULL ,
[BeginMonth] int NULL ,
[BeginYear] int NULL ,
[CreateDate] datetime NULL ,
[CreateUserID] varchar(20) NULL ,
[LastModifyDate] datetime NULL ,
[LastModifyUserID] varchar(20) NULL ,
[DivisionNameE] varchar(250) NULL ,
[AddressE] varchar(250) NULL ,
[ImageLogo] NTEXT NULL ,
[Logo] image NULL ,
[DirectorName] nvarchar(100) NULL ,
[ChiefAccountantName] nvarchar(100) NULL ,
[StoreKeeperName] nvarchar(100) NULL ,
[CashierName] nvarchar(100) NULL ,
[ReportCreatorName1] nvarchar(100) NULL ,
[ReportCreatorName2] nvarchar(100) NULL ,
[ReportCreatorName3] nvarchar(100) NULL ,
[ReportCreatorName4] nvarchar(100) NULL ,
[ReportCreatorName5] nvarchar(100) NULL ,
[Info1] nvarchar(500) NULL ,
[Info2] nvarchar(500) NULL ,
[Info3] nvarchar(500) NULL ,
[Info4] nvarchar(500) NULL ,
[Info5] nvarchar(500) NULL ,
[Representative] nvarchar(100) NULL ,
[Website] nvarchar(100) NULL ,
[BankAccountNo] varchar(30) NULL 
)


GO

-- ----------------------------
-- Indexes structure for table tb0038
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table tb0038
-- ----------------------------
ALTER TABLE [dbo].[tb0038] ADD PRIMARY KEY NONCLUSTERED ([DivisionID])
GO

-- ----------------------------
-- Triggers structure for table tb0038
-- ----------------------------


-------------- [TRIGGER 1]

--DROP TRIGGER [dbo].[AX1101]
GO
CREATE TRIGGER [dbo].[AX1101]
ON [dbo].[tb0038]
AFTER DELETE
AS

------ Created by Nguyen Van Nhan,  Date 01/09/2003
Delete tb0199 
From tb0199  inner join DELETED Del on Del.DivisionID = tb0199.DivisionID



Delete tb3060 
From tb3060  inner join DELETED Del on Del.DivisionID = tb3060.DivisionID


if Exists (Select top 1 1 from SysObjects Where Xtype ='U' and Name = 'tb2646' )
Delete tb2646 
From tb2646  inner join DELETED Del on Del.DivisionID =tb2646.DivisionID

---Created by: Vo Thanh Huong, date: 22/10/2004
if Exists (Select top 1 1 from SysObjects Where Xtype ='U' and Name = 'tb3557' )
DELETE tb3557
From tb3557 inner join DELETED Del on Del.DivisionID = tb3557.DivisionID

---Created by: Nguyen Thi Thuy Tuyen, date: 2/11/2006
if Exists (Select top 1 1 from SysObjects Where Xtype ='U' and Name = 'tb0522' )
DELETE tb0522
From tb0522 inner join DELETED Del on Del.DivisionID = tb0522.DivisionID

Delete tb0076
From tb0076 inner join DELETED Del on Del.DivisionID = tb0076.DivisionID


---Created by: Nguyen Quoc Huy, date: 13/06/2007 ----- Module TSCD --------
if Exists (Select top 1 1 from SysObjects Where Xtype ='U' and Name = 'tb2001' )
DELETE tb2001
From tb2001 inner join DELETED Del on Del.DivisionID = tb2001.DivisionID

if Exists (Select top 1 1 from SysObjects Where Xtype ='U' and Name = 'tb4502' )
DELETE tb4502
From tb4502 inner join DELETED Del on Del.DivisionID = tb4502.DivisionID

-------------- [TRIGGER 2]

GO
--DROP TRIGGER [dbo].[AY1101]
GO
CREATE TRIGGER [dbo].[AY1101]
ON [dbo].[tb0038]
AFTER INSERT
AS

If not Exists (Select 1 From tb0000)
Insert   tb0000  (DefDivisionID, DefTranMonth, DefTranYear, ScheduleDays, StartHour,   StartMinute ,EndHour,     EndMinute)
Select DivisionID, BeginMonth, BeginYear, 4, 8,0,17,0
From Inserted

If Not Exists (Select 1 From tb0199 inner join Inserted Ins on Ins.DivisionID = tb0199.DivisionID)
Insert tb0199 (DivisionID,TranMonth,TranYear, Closing, BeginDate, EndDate )
Select DivisionID, BeginMonth, BeginYear, 0,  ltrim(rtrim(str(BeginMonth)))+'/01/'+ltrim(rtrim(str(BeginYear))) , DATEADD(day,-1, ltrim(rtrim(str(Case when BeginMonth = 12 then 1 else BeginMonth+1 End )))+'/01/'+ltrim(rtrim(str(Case when  BeginMonth = 12  then BeginYear+ 1 else BeginYear End ))) ) 
From Inserted

----- Them vao AsoftM

If not Exists (Select 1 From tb3000)
Insert   tb3000  (DivisionID, TranMonth, TranYear, OriginalDecimal, ConvertDecimal, UnitPriceDecimal, PercentDecimal, QuantityDecimal)
Select DivisionID, BeginMonth, BeginYear,0,0,0,0,0
From Inserted

If Not Exists (Select 1 From tb3060 inner join Inserted Ins on Ins.DivisionID = tb3060.DivisionID)
Insert tb3060 (DivisionID,TranMonth,TranYear, Closing, BeginDate, EndDate )
Select DivisionID, BeginMonth, BeginYear, 0,  ltrim(rtrim(str(BeginMonth)))+'/01/'+ltrim(rtrim(str(BeginYear))) , DATEADD(day, -1, DATEADD(MONTH, 1, ltrim(rtrim(str(BeginMonth))) + '/01/' + ltrim(rtrim(str(BeginYear)))))
From Inserted


----- Asoft HRM -----
If exists(Select Top 1 1 From sysObjects Where XType = 'U' and Name = 'tb2500')
Begin
If not Exists (Select 1 From tb2500)
Insert   tb2500  (DivisionID, TranMonth, TranYear,  TimeConvert)
Select DivisionID, BeginMonth, BeginYear,8
From Inserted

If Not Exists (Select 1 From tb2646 inner join Inserted Ins on Ins.DivisionID = tb2646.DivisionID)
Insert tb2646 (DivisionID,TranMonth,TranYear, Closing, BeginDate, EndDate )
Select DivisionID, BeginMonth, BeginYear, 0,  ltrim(rtrim(str(BeginMonth)))+'/01/'+ltrim(rtrim(str(BeginYear))) , DATEADD(day, -1, DATEADD(MONTH, 1, ltrim(rtrim(str(BeginMonth))) + '/01/' + ltrim(rtrim(str(BeginYear)))))
From Inserted
End


----Created by: Vo Thanh Huong, date: 22/10/2004
----- Asoft OP -----
If exists(Select Top 1 1 From sysObjects Where XType = 'U' and Name = 'tb3500')
Begin
If not Exists (Select 1 From tb3500)
Insert  tb3500  (DivisionID, TranMonth, TranYear)
Select DivisionID, BeginMonth, BeginYear
From Inserted

If Not Exists (Select 1 From tb3557 inner join Inserted Ins on Ins.DivisionID = tb3557.DivisionID)
Insert tb3557 (DivisionID,TranMonth,TranYear, Closing, BeginDate, EndDate )
Select DivisionID, BeginMonth, BeginYear, 0,  ltrim(rtrim(str(BeginMonth)))+'/01/'+ltrim(rtrim(str(BeginYear))) , DATEADD(day, -1, DATEADD(MONTH, 1, ltrim(rtrim(str(BeginMonth))) + '/01/' + ltrim(rtrim(str(BeginYear)))))
From Inserted
End
----Created by: Nguyen Thuy Tuyen, date: 02/11/2006
----- Asoft CS -----
If exists(Select Top 1 1 From sysObjects Where XType = 'U' and Name = 'tb0500')
Begin
If not Exists (Select 1 From tb0500)
Insert  tb0500  (DefDivisionID, DefTranMonth, DefTranYear)
Select DivisionID, BeginMonth, BeginYear
From Inserted

If Not Exists (Select 1 From tb0522 inner join Inserted Ins on Ins.DivisionID = tb0522.DivisionID)
Insert tb0522 (DivisionID,TranMonth,TranYear, Closing, BeginDate, EndDate )
Select DivisionID, BeginMonth, BeginYear, 0,  ltrim(rtrim(str(BeginMonth)))+'/01/'+ltrim(rtrim(str(BeginYear))) , DATEADD(day, -1, DATEADD(MONTH, 1, ltrim(rtrim(str(BeginMonth))) + '/01/' + ltrim(rtrim(str(BeginYear)))))
From Inserted
End


----Created by: Nguyen Quoc Huy, date: 13/06/2007
----- Asoft FA -----
If exists(Select Top 1 1 From sysObjects Where XType = 'U' and Name = 'tb2000')
Begin
If not Exists (Select 1 From tb2000)
Insert  tb2000  (DefDivisionID, DefTranMonth, DefTranYear)
Select DivisionID, BeginMonth, BeginYear
From Inserted

If Not Exists (Select 1 From tb2001 inner join Inserted Ins on Ins.DivisionID = tb2001.DivisionID)
Insert tb2001 (DivisionID,TranMonth,TranYear, Closing, BeginDate, EndDate )
Select DivisionID, BeginMonth, BeginYear, 0,  ltrim(rtrim(str(BeginMonth)))+'/01/'+ltrim(rtrim(str(BeginYear))) , DATEADD(day, -1, DATEADD(MONTH, 1, ltrim(rtrim(str(BeginMonth))) + '/01/' + ltrim(rtrim(str(BeginYear)))))
From Inserted
End


----- Asoft WM -----
If exists(Select Top 1 1 From sysObjects Where XType = 'U' and Name = 'tb4500')
Begin
If not Exists (Select 1 From tb4500)
Insert  tb4500  (DefDivisionID, DefTranMonth, DefTranYear)
Select DivisionID, BeginMonth, BeginYear
From Inserted

If Not Exists (Select 1 From tb4502 inner join Inserted Ins on Ins.DivisionID = tb4502.DivisionID)
Insert tb4502 (DivisionID,TranMonth,TranYear, Closing, BeginDate, EndDate )
Select DivisionID, BeginMonth, BeginYear, 0,  ltrim(rtrim(str(BeginMonth)))+'/01/'+ltrim(rtrim(str(BeginYear))) , DATEADD(day, -1, DATEADD(MONTH, 1, ltrim(rtrim(str(BeginMonth))) + '/01/' + ltrim(rtrim(str(BeginYear)))))
From Inserted
End

If not exists (Select top 1 1 From tb0076  inner join inserted Ins on 	ins.DivisionID = tb0076.DivisionID )

Insert tb0076 (DivisionID, ModuleID, DataID, DataName , DataType ,CreateDate,CreateUserID, LastModifyUserID,LastModifyDate)
Select DivisionID, ModuleID, (Case When  BeginMonth <10 then '0'+rtrim(ltrim(str(BeginMonth)))+'/'+ltrim(Rtrim(str(BeginYear))) 
	Else rtrim(ltrim(str(BeginMonth)))+'/'+ltrim(Rtrim(str(BeginYear))) End) as BeginYear,
(Case When  BeginMonth <10 then '0'+rtrim(ltrim(str(BeginMonth)))+'/'+ltrim(Rtrim(str(BeginYear))) 
	Else rtrim(ltrim(str(BeginMonth)))+'/'+ltrim(Rtrim(str(BeginYear))) End) as MonthYear,'PE',CreateDate,CreateUserID, LastModifyUserID,LastModifyDate
From Inserted ins, tb0078


GO

-- ----------------------------
-- Records of tb0038
-- ----------------------------
INSERT INTO [dbo].[tb0038] ([DivisionID], [DivisionName], [Disabled], [Tel], [Fax], [Email], [Address], [ContactPerson], [VATNO], [BeginMonth], [BeginYear], [CreateDate], [CreateUserID], [LastModifyDate], [LastModifyUserID], [DivisionNameE], [AddressE], [ImageLogo], [Logo], [DirectorName], [ChiefAccountantName], [StoreKeeperName], [CashierName], [ReportCreatorName1], [ReportCreatorName2], [ReportCreatorName3], [ReportCreatorName4], [ReportCreatorName5], [Info1], [Info2], [Info3], [Info4], [Info5], [Representative], [Website], [BankAccountNo]) VALUES (N'HPCNB2013', N'Công ty ABC Tiếng Việt', N'0', N'', N'', N'', N'', N'', N'', N'1', N'2013', N'2012-12-27 20:42:59.630', N'', N'2014-04-15 19:57:54.520', N'ADMIN', N'', N'', null, null, N'', N'', N'', N'', N'', N'', null, null, null, N'thong tin 1 rr g cong hoa xa hoi chu nghia viet nam, doc lap tu do hanh phuc', N'thong tin 2 rr g cong hoa xa hoi chu nghia viet nam, doc lap tu do hanh phuc', N'thong tin 3 rr g cong hoa xa hoi chu nghia viet nam, doc lap tu do hanh phuc', N'thong tin 4 rr g cong hoa xa hoi chu nghia viet nam, doc lap tu do hanh phuc', N'thong tin 5 rr g cong hoa xa hoi chu nghia viet nam, doc lap tu do hanh phuc', N'', N'', N'')
GO
GO